﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Dapper;

namespace ComTools
{
    public partial class frmTableEntity : Form
    {
        public frmTableEntity()
        {
            InitializeComponent();
        }

        private String _ConnStr = null;

        private IDbConnection dbConnection
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_ConnStr))
                {
                    var conf = System.Configuration.ConfigurationManager.ConnectionStrings["U9DB"];
                    _ConnStr = conf.ConnectionString;
                }
                return new SqlConnection(_ConnStr);
            }
        }

        private string GetErrMsg(Exception exc)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(exc.Message);
            var eerr = exc.InnerException;
            while (eerr != null)
            {
                sb.AppendLine(eerr.Message);
            }
            return sb.ToString();
        }

        private DataTable Query(string tableName, string tableDesc, out string errMsg)
        {
            DataTable table = new DataTable();
            try
            {
                using (var conn = dbConnection)
                {
                    var strSql = @"
SELECT a.[FullName] as FullName,a.[Name] as Name,b.[DisplayName] as DisplayName,a.DefaultTableName  as DefaultTableName,
	    a.[ClassType] as ClassType,a.[ID], c.AssemblyName
FROM [UBF_MD_Class] as a  
INNER join UBF_MD_Class_trl as b  on a.Local_ID=b.Local_ID 
	and b.sysmlflag='zh-CN' 
left join UBF_MD_Component as c on a.MD_Component_ID=c.ID
where a.ClassType in(1,3)  	
	AND (@TableDesc = '' or a.Name like '%'+@TableDesc+'%' or  b.DisplayName like '%'+@TableDesc+'%')  
	AND (@TableName = '' or a.DefaultTableName = @TableName)
order by Name";
                    conn.Open();

                    var ireader = conn.ExecuteReader(strSql, new
                    {
                        TableDesc = tableDesc,
                        TableName = tableName
                    });

                    table.Load(ireader);

                    conn.Close();
                }
                errMsg = string.Empty;
            }
            catch (Exception exc)
            {
                errMsg = GetErrMsg(exc);
            }
            return table;
        }

        string errMsg;
        private void btnQuery_Click(object sender, EventArgs e)
        {
            var table = Query(txtTableName.Text.Trim(), txtTableDesc.Text.Trim(), out errMsg);

            dataGridView1.DataSource = table;

            if (!string.IsNullOrWhiteSpace(errMsg))
            {
                MessageBox.Show(errMsg);
            }
        }


        private void FilterTable(DataTable table1, string filter)
        {
            //var table1 = dataGridView1.DataSource as DataTable;
            if (table1 == null) { return; }
            filter = txtFilter.Text.Trim();
            if (!string.IsNullOrWhiteSpace(filter))
            {
                table1.DefaultView.RowFilter = "";
                return;
            }
            //
            List<string> columns = new List<string>();
            if (columns.Count == 0)
            {
                foreach (DataColumn dc in table1.Columns)
                {
                    if (dc.DataType == typeof(string))
                    {
                        columns.Add(dc.ColumnName);
                    }
                }
            }
            //
            StringBuilder sb = new StringBuilder();
            foreach (string c in columns)
            {
                if (sb.Length > 0)
                {
                    sb.Append(" or ");
                }
                sb.Append(c + " like '%" + filter + "%'");
            }
            //
            table1.DefaultView.RowFilter = sb.ToString();
        }

        private void txtFilter_TextChanged(object sender, EventArgs e)
        {
            var table = dataGridView1.DataSource as DataTable;
            FilterTable(table, txtFilter.Text.Trim());
        }

        private DataTable QueryByClassId(string classId, out string errMsg)
        {
            DataTable table = new DataTable();
            try
            {
                using (var conn = dbConnection)
                {
                    var strSql = @"
SELECT  a.[Name] as Name, a.DataTypeID as ID,b.FullName as FullName, a.DefaultValue as DefaultValue ,
        a.IsCollection,c.DisplayName as DisplayName,c.[Description] as Description,
        b.ClassType  as ClassType  ,a.IsKey ,a.IsNullable ,a.IsReadOnly,a.IsSystem,a.IsBusinessKey,a.GroupName
from UBF_MD_Attribute  a  
inner join UBF_MD_CLASS b ON a.DataTypeID =b.ID  
left join UBF_MD_Attribute_trl as c on a.Local_ID=c.Local_ID and ( c.sysmlflag='zh-CN' or c.sysmlflag is null ) 
where MD_Class_ID= @ClassId 
order by a.IsSystem desc,a.GroupName asc , a.[Name] asc
";
                    conn.Open();

                    var ireader = conn.ExecuteReader(strSql, new
                    {
                        ClassId = classId
                    });

                    table.Load(ireader);

                    conn.Close();
                }
                errMsg = string.Empty;
            }
            catch (Exception exc)
            {
                errMsg = GetErrMsg(exc);
            }
            return table;
        }

        private void txt2Filter_TextChanged(object sender, EventArgs e)
        {
            var table = dataGridView2.DataSource as DataTable;
            FilterTable(table, txt2Filter.Text.Trim());
        }

        private void dataGridView1_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            var gridRow = dataGridView1.SelectedCells[0].OwningRow;
            var rowView = gridRow.DataBoundItem as DataRowView;
            var id  = rowView["ID"].ToString();
            string errMsg;
            tabControl1.SelectedIndex++;
            var table = QueryByClassId(id, out errMsg);
            dataGridView2.DataSource = table;
            if (!string.IsNullOrEmpty(errMsg))
            {
                MessageBox.Show(errMsg);
            }
        }
    }
}